﻿<%@ WebHandler Language="C#" Class="FetchCommonHandler" %>

/*Description:通用fetchAPI接口
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan 
'create Date:2024.1.22
*/
using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BusinessRule.DTO;
using DatabaseAccess.DBUtility;
using System.Configuration;
using System.Net;

public class FetchCommonHandler : IHttpHandler
{
    string userImagesPath = System.Configuration.ConfigurationManager.AppSettings["UserImagesPath"];
    DataTable getUserImage(DataTable dt, string colName, string username_digit8, string username_digit6)
    {
        string url = "";
        HttpWebRequest re = null;
        HttpWebResponse res = null;
        string errMsg = "";

        foreach (DataRow dr in dt.Rows)
        {
            //先查找8位
            try
            {
                url = userImagesPath + dr[username_digit8].ToString() + ".JPG";
                re = (HttpWebRequest)WebRequest.Create(url);
                res = (HttpWebResponse)re.GetResponse();
                if (res.ContentLength != 0)
                {
                    dr[colName] = url;
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            finally
            {
                if (re != null) re.Abort();
                if (res != null) res.Close();
            }

            //8位未找到再找6位
            if (dr[colName].ToString() == "" && dr[username_digit6].ToString() != "")
            {
                try
                {
                    url = userImagesPath + dr[username_digit6].ToString() + ".JPG";
                    re = (HttpWebRequest)WebRequest.Create(url);
                    res = (HttpWebResponse)re.GetResponse();
                    if (res.ContentLength != 0)
                    {
                        dr[colName] = url;
                    }
                }
                catch (Exception ex)
                {
                    errMsg = ex.Message;
                }
                finally
                {
                    if (re != null) re.Abort();
                    if (res != null) res.Close();
                }
            }
        }
        return dt;
    }

    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (System.IO.StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                uMESProcessResult r = new uMESProcessResult();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getUomList":
                        r = getUomList(jObject);//查询uom
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUserInfo":
                        r = getUserInfo(jObject);//查询用户信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryUsersByRoleName":
                        r = getFactoryUsersByRoleName(jObject);//根据角色查询分厂人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryUserattributes":
                        r = getFactoryUserattributes(jObject);//查询分厂属性
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMfgordernameAsync":
                        r = getMfgordernameAsync(jObject);//模糊匹配获取订单号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getProductnameAsync":
                        r = getProductnameAsync(jObject);//模糊匹配零件图号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainernameAsync":
                        r = getContainernameAsync(jObject);//模糊匹配批次号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getLossReasonByGroupName":
                        r = getLossReasonByGroupName(jObject);//根据组名获取报废原因
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUnqualifiedFeatureList":
                        r = getUnqualifiedFeatureList(jObject);//获取不合格品特征
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryListByCode":
                        r = getFactoryListByCode(jObject);//查询分厂
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                uMESProcessResult r = new uMESProcessResult() { ReturnData = null, RowCount = "0", Code = "500", Message = e.Message };
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    uMESProcessResult getUomList(JObject jObject)
    {
        string sql = @"SELECT u.uomid,u.uomname FROM uom u";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = "100", Code = "200", Message = "请求成功" };
    }

    uMESProcessResult getUserInfo(JObject jObject)
    {
        string sql = @"
SELECT e.employeename,e.employeeid,e.fullname,
       f.factoryname,f.factoryid,fm.factorycode,
       wc.workcentername,wc.workcenterid,
       s.shiftname,NVL(e.shiftid,te.shiftid) shiftid,
       te.teamname,te.teamid,
       eroles.rolenames,mp.liuwei
FROM employee e 
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN mes_factory_mapping fm ON fm.factoryid = f.factoryid
LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid 
LEFT JOIN 
( 
  SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames
  FROM employeerole er 
  LEFT JOIN employee e ON e.employeeid = er.employeeid  
  LEFT JOIN roledef r ON r.roleid = er.roleid  
  GROUP BY e.employeeid 
) eroles ON eroles.employeeid = e.employeeid 
LEFT JOIN 
(
  SELECT  t.teamname,t.teamid,te.employeeid,t.shiftid 
  FROM teamemployee te 
  LEFT JOIN team t ON t.teamid = te.teamid 
) te  ON te.employeeid = e.employeeid AND ROWNUM = 1
LEFT JOIN shift s ON s.shiftid = NVL(e.shiftid,te.shiftid)
LEFT JOIN mes_userimage_mapping mp ON mp.employeename = e.employeename
WHERE 1=1";
        if (jObject["employeeid"] != null && jObject["employeeid"].ToString() != "")
        {
            sql += string.Format(" AND e.employeeid = '{0}'", jObject["employeeid"].ToString());
        }
        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(e.employeename) = '{0}'", jObject["employeename"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        dt.Columns.Add("userimageurl", typeof(string));
        //查找用户头像信息
        dt = getUserImage(dt, "userimageurl", "employeename", "liuwei");
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = "100", Code = "200", Message = "请求成功" };
    }

    //根据角色查询分厂人员
    uMESProcessResult getFactoryUsersByRoleName(JObject jObject)
    {
        string sql = @"
SELECT DISTINCT e.employeename,e.employeeid,e.fullname
FROM roledef rd
LEFT JOIN employeerole er ON er.roleid = rd.roleid
LEFT JOIN employee e ON e.employeeid = er.employeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE e.fullname IS NOT NULL ";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["rolename"] != null && jObject["rolename"].ToString() != "")
        {
            string rolename = jObject["rolename"].ToString();
            if (rolename.IndexOf('|') != -1)
            {
                string[] list = rolename.Split('|');
                string roles = "(";
                foreach (var i in list)
                {
                    roles += "'" + i + "',";
                }
                roles = roles.TrimEnd(',');
                roles += ")";
                sql += string.Format(" AND rd.rolename IN {0}", roles);
            }
            else
            {
                sql += string.Format(" AND rd.rolename = '{0}'", jObject["rolename"].ToString());
            }
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = "100", Code = "200", Message = "请求成功" };
    }

    //查询分厂属性
    uMESProcessResult getFactoryUserattributes(JObject jObject)
    {
        string sql = @"SELECT u.userattributename,u.attributevalue FROM userattribute u WHERE u.parentid = '{0}'";
        string factoryid = jObject["factoryid"] != null ? jObject["factoryid"].ToString() : "";
        sql = string.Format(sql, factoryid);
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }

    //模糊匹配获取订单号
    uMESProcessResult getMfgordernameAsync(JObject obj)
    {
        string sql = @" SELECT rownum,mfg.mfgordername FROM mfgorder mfg";
        if (obj["mfgordername"] != null && obj["mfgordername"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(mfg.mfgordername) LIKE '%{0}%'", obj["mfgordername"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }

    //模糊匹配获取零件号
    uMESProcessResult getProductnameAsync(JObject jObject)
    {
        string sql = @" SELECT rownum,pb.productname FROM productbase pb";
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(pb.productname) LIKE '%{0}%'", jObject["productname"].ToString().ToUpper());
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }

    //模糊匹配获取批次号
    uMESProcessResult getContainernameAsync(JObject jObject)
    {
        string sql = @" SELECT rownum,cci.containerid,cci.containername FROM containercurrentinfo cci";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(cci.containername) LIKE '{0}%'", jObject["containername"].ToString().ToUpper());
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }

    //根据组名获取报废原因
    uMESProcessResult getLossReasonByGroupName(JObject obj)
    {
        string sql = @"
SELECT lr.lossreasonid,lr.lossreasonname,lr.description,lr.lossreasonname || lr.description fecod
FROM lossreasongroup l
LEFT JOIN namedobjectgroupentries n ON n.namedobjectgroupid = l.lossreasongroupid
LEFT JOIN lossreason lr ON lr.lossreasonid = n.entriesid
WHERE l.lossreasongroupname = '{0}'
ORDER BY n.sequence";

        if (obj["lossreasongroupname"] != null && obj["lossreasongroupname"].ToString() != "")
        {
            sql = string.Format(sql, obj["lossreasongroupname"].ToString());
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }

    //获取不合格品特征
    uMESProcessResult getUnqualifiedFeatureList(JObject obj)
    {
        string sql = @"SELECT u.* FROM unqualifiedfeature u WHERE u.factoryid = '{0}' AND u.isuse = 0";
        sql = string.Format(sql, obj["factoryid"].ToString());
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }

    uMESProcessResult getFactoryListByCode(JObject obj)
    {
        string sql = "";
        if (obj["isMapping"] != null && obj["isMapping"].ToString() == "是")
        {
            sql = @"
SELECT f.factoryid,f.factoryname,f.factorycode attributevalue
FROM mes_factory_mapping f
WHERE f.factorycode IS NOT NULL ";
            if (obj["factoryCode"] != null && obj["factoryCode"].ToString() != "")
            {
                string factoryCode = obj["factoryCode"].ToString();
                if (factoryCode.IndexOf('|') != -1)
                {
                    string[] list = factoryCode.Split('|');
                    string roles = "(";
                    foreach (var i in list)
                    {
                        roles += "'" + i + "',";
                    }
                    roles = roles.TrimEnd(',');
                    roles += ")";
                    sql += string.Format(" AND f.factorycode IN {0}", roles);
                }
                else
                {
                    sql += string.Format(" AND f.factorycode = '{0}'", obj["factoryCode"].ToString());
                }
            }

            sql += " ORDER BY f.factorycode ";
        }
        else
        {
            sql = @"
SELECT f.factoryid,f.factoryname,u.attributevalue
FROM factory f
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE u.attributevalue IS NOT NULL ";
            if (obj["factoryCode"] != null && obj["factoryCode"].ToString() != "")
            {
                string factoryCode = obj["factoryCode"].ToString();
                if (factoryCode.IndexOf('|') != -1)
                {
                    string[] list = factoryCode.Split('|');
                    string roles = "(";
                    foreach (var i in list)
                    {
                        roles += "'" + i + "',";
                    }
                    roles = roles.TrimEnd(',');
                    roles += ")";
                    sql += string.Format(" AND u.attributevalue IN {0}", roles);
                }
                else
                {
                    sql += string.Format(" AND u.attributevalue = '{0}'", obj["factoryCode"].ToString());
                }
            }

            sql += " ORDER BY u.attributevalue ";
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return new uMESProcessResult() { ReturnData = dt, RowCount = dt.Rows.Count.ToString(), Code = "200", Message = "请求成功" };
    }
}